package dao;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import bean.User;

public interface UserDAO {
    /*
     * @Insert用于注解方法，表示此方法是一个插入语句
     * 当sql语句涉及mybatis标签时，整个语句必须包含在script标签内
     */
    @Insert("<script>INSERT INTO users(name,sex,bir,tel,eml,addr)"
        + " VALUES(#{name},#{sex},"
        + "<choose>"
            + "<when test=\"birthdate!=null and birthdate!=''\">TO_DATE(#{birthdate},'yyyy-MM-dd'),</when>"
            + "<otherwise>null,</otherwise>"
        + "</choose>"
        + "<choose>"
            + "<when test=\"telephone!=null and telephone!=''\">#{telephone},</when>"
            + "<otherwise>null,</otherwise>"
        + "</choose>"
        + "<choose>"
            + "<when test=\"email!=null and email!=''\">#{email},</when>"
            + "<otherwise>null,</otherwise>"
        + "</choose>"
        + "<choose>"
            + "<when test=\"address!=null and address!=''\">#{address}</when>"
            + "<otherwise>null</otherwise>"
        + "</choose>)"
        + "</script>")
    boolean add(User temp);
    
    /*
     * @Update用于注解方法，表示此方法是一个更新语句
     * 当sql语句涉及mybatis标签时，整个语句必须包含在script标签内
     */
    @Update("<script>UPDATE users <set>"
        + "<if test=\"name!=null and name!=''\">name=#{name},</if>"
        + "<if test=\"sex!=null and sex!=''\">sex=#{sex},</if>"
        + "<choose>"
        + "<when test=\"birthdate!=null and birthdate!=''\">bir=TO_DATE(#{birthdate},'yyyy-MM-dd'),</when>"
        + "<otherwise>bir=null,</otherwise>"
        + "</choose>"
        + "tel=#{telephone},"
        + "eml=#{email},"
        + "addr=#{address}"
        + " WHERE id=#{id}"
        + "</set></script>")
    boolean mod(User temp);
    
    /*
     * @Delete用于注解方法，表示此方法是一个删除语句
     */
    @Delete("DELETE FROM users WHERE id=#{val}")
    boolean rem(int integer);
    
    /*
     * @Results用于注解方法，它的作用是对属性进行分组映射定义，类似mapper中的<resultMap>
     * id属性值表示此resultMap的编号
     */
    @Results(id = "userMap", value = {
        @Result(property = "id", column = "id"),
        @Result(column = "name", property = "name"),
        @Result(column = "sex", property = "sex"),
        @Result(column = "bir", property = "birthday"),
        @Result(column = "tel", property = "telephone"),
        @Result(column = "eml", property = "email"),
        @Result(column = "addr", property = "address")
    })
    @Select(value = "SELECT users.* FROM users WHERE users.id=#{val}")
    User selById(int integer);

    /*
     * 引用已有的结果映射
     */
    @ResultMap(value = "userMap")
    @Select(value = "<script>SELECT * FROM users<where>"
        + "<if test=\"name!=null and name!=''\"> name LIKE CONCAT(CONCAT('%',#{name}),'%')</if>"
        + "<if test=\"sex!=null and sex!=''\"> AND sex=#{sex}</if>"
        + "<if test=\"telephone!=null and telephone!=''\"> AND tel LIKE CONCAT(CONCAT('%',#{telephone}),'%')</if>"
        + "<if test=\"email!=null and email!=''\"> AND eml LIKE CONCAT(CONCAT('%',#{email}),'%')</if>"
        + "<if test=\"address!=null and address!=''\"> AND addr LIKE CONCAT(CONCAT('%',#{address}),'%')</if>"
        + "</where>"
        + " ORDER BY id</script>")
    List<User> selByOthers(User temp);
}
